package org.example;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import us.codecraft.webmagic.ResultItems;
import us.codecraft.webmagic.Task;
import us.codecraft.webmagic.pipeline.Pipeline;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Component
@Slf4j
public class SqlPipeline implements Pipeline {
    static String driver = "com.mysql.jdbc.Driver";
    static String url = "jdbc:mysql://localhost:3306/test_area?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
    static String username = "root";
    static String password = "root";
    static Connection conn = null;

    static{
        try {
            Class.forName(driver); //classLoader,加载对应驱动
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void process(ResultItems resultItems, Task task) {
        List<Map<String, Object>> area = resultItems.get("area");
        System.out.println("地区总数：{}"+  area.size());
        if (area.size() == 0) {
            System.out.println(resultItems.getRequest().getUrl() + " 此页面未爬取数据,请稍后重试!");
        } else {
            area.stream().forEach(stringObjectMap -> {
                System.out.println("{}"+ stringObjectMap);
                //String sql = "insert into sys_area ('id','is_deleted','create_date', 'edit_date', 'creator', 'editor','parent','level','code','name','full_name','is_leaf','parentcode','yn','remark') VALUES (?, ?, ?, ?, ?, ?)";
                String sql = "insert into sys_area VALUES (?, 0, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, 1, ?)";
                PreparedStatement preparedStatement;
                try {
                    preparedStatement = conn.prepareStatement(sql);

                    preparedStatement.setString(1, UUIDUtils.generate());
//                    preparedStatement.setString(2,  );//类型bit(1)
                    preparedStatement.setString(2, String.valueOf(new Date().getTime()));
                    preparedStatement.setString(3, String.valueOf(new Date().getTime()));
                    preparedStatement.setString(4, "");
                    preparedStatement.setString(5, "");
                    preparedStatement.setString(6, stringObjectMap.get("C_CASCADE").toString()); //parent：级联关系
                    preparedStatement.setString(7, stringObjectMap.get("C_LEVEL").toString()); //level:province,city,county
                    preparedStatement.setString(8, stringObjectMap.get("C_CODE").toString()); //code
                    preparedStatement.setString(9, stringObjectMap.get("C_NAME").toString()); //name
                    preparedStatement.setString(10, stringObjectMap.get("C_FULL_NAME").toString()); //full_name
                    preparedStatement.setString(11, null); //is_leaf
                    preparedStatement.setString(12, stringObjectMap.get("C_PARENT_CODE").toString()); //parentcode
//                    preparedStatement.setString(13, "1"); //yn
                    preparedStatement.setString(13, stringObjectMap.get("C_YEAR").toString()); //remark

                    preparedStatement.executeUpdate();
                }catch (SQLException e) {
                    System.out.println("插入数据库错误:{}"+ e.getMessage()+ e);
                }
            });


        }

    }
}
